
#! /bin/bash
export LANG=zh_CN.UTF-8
PRESTO_HOME=/export/server/presto/bin/presto


${PRESTO_HOME} --catalog hive --server 192.168.88.80:8090 --execute "
INSERT INTO edu_online_dws.dws_visit_consult_data_count
SELECT
    "day",
    "month",
    quarter,
    "year",
    country,
    province,
    city,
    origin_channel,
    seo_source,
    from_url,
    CASE
        WHEN GROUPING(day,country) = 0 THEN 'day_country'
        WHEN GROUPING(day,province) = 0 THEN 'day_province'
        WHEN GROUPING(day,city) = 0 THEN 'day_city'
        WHEN GROUPING(day,origin_channel) = 0 THEN 'day_origin_channel'
        WHEN GROUPING(day,seo_source) = 0 THEN 'day_seo_source'
        WHEN GROUPING(day,from_url) = 0 THEN 'day_from_url'
        WHEN GROUPING(day) = 0 THEN 'day'
        WHEN GROUPING(month,country) = 0 THEN 'month_country'
        WHEN GROUPING(month,province) = 0 THEN 'month_province'
        WHEN GROUPING(month,city) = 0 THEN 'month_city'
        WHEN GROUPING(month,origin_channel) = 0 THEN 'month_origin_channel'
        WHEN GROUPING(month,seo_source) = 0 THEN 'month_seo_source'
        WHEN GROUPING(month,from_url) = 0 THEN 'month_from_url'
        WHEN GROUPING(month) = 0 THEN 'month'
        WHEN GROUPING(year,quarter,country) = 0 THEN 'quarter_country'
        WHEN GROUPING(year,quarter,province) = 0 THEN 'quarter_province'
        WHEN GROUPING(year,quarter,city) = 0 THEN 'quarter_city'
        WHEN GROUPING(year,quarter,origin_channel) = 0 THEN 'quarter_origin_channel'
        WHEN GROUPING(year,quarter,seo_source) = 0 THEN 'quarter_seo_source'
        WHEN GROUPING(year,quarter,from_url) = 0 THEN 'quarter_from_url'
        WHEN GROUPING(year,quarter) = 0 THEN 'quarter'
        WHEN GROUPING(year,country) = 0 THEN 'year_country'
        WHEN GROUPING(year,province) = 0 THEN 'year_province'
        WHEN GROUPING(year,city) = 0 THEN 'year_city'
        WHEN GROUPING(year,origin_channel) = 0 THEN 'year_origin_channel'
        WHEN GROUPING(year,seo_source) = 0 THEN 'year_seo_source'
        WHEN GROUPING(year,from_url) = 0 THEN 'year_from_url'
        WHEN GROUPING(year) = 0 THEN 'year' ELSE 'other'
        END AS group_type,

    count (DISTINCT sid) AS visit_person,
    COUNT (DISTINCT session_id) AS visit_dialogue,
    COUNT (DISTINCT ip) AS visit_ip,
    CASE WHEN country IS NOT NULL THEN COUNT (msg_count)*1.0000/count (sid)*100 ELSE NULL END AS country_consult_rate,
    CASE WHEN province IS NOT NULL THEN COUNT (msg_count)*1.0000/count (sid)*100 ELSE NULL END AS province_consult_rate,
    CASE WHEN city IS NOT NULL THEN COUNT (msg_count)*1.0000/count (sid)*100 ELSE NULL END AS city_consult_rate
FROM edu_online_dwb.dwb_visit_consult_detail
GROUP BY
    GROUPING SETS ("day",   --天
                    ("day", country),
                    ("day", province),
                    ("day", city),
                    ("day", origin_channel),
                    ("day", from_url),
                    ("day", seo_source),
                    "month", --月
                    ("month", country),
                    ("month", province),
                    ("month", city),
                    ("month", origin_channel),
                    ("month", from_url),
                    ("month", seo_source),
                    ("year",quarter), --季度
                    ("year",quarter, country),
                    ("year",quarter, province),
                    ("year",quarter, city),
                    ("year",quarter, origin_channel),
                    ("year",quarter, from_url),
                    ("year",quarter, seo_source),
                    "year",  --年
                    ("year", country),
                    ("year", province),
                    ("year", city),
                    ("year", origin_channel),
                    ("year", from_url),
                    ("year", seo_source)
    );
"